import scrapy
import pymysql

class HouseSpider(scrapy.Spider):
    name = "house"
    allowed_domains = ["erp.fangline.cn"]
    start_urls = ["https://erp.fangline.cn"]

    # 查询数据
    def fetch_data(param):
        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        sql = "select * from house where `section` ='一中家属区' and house_id not in (select house_id from house_follow where (content like '%不卖了%') and (content like '%联系不上%')) limit 30;"
        cursor = conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        cursor.close()
        conn.close()
        return data

    def parse(self, response):
        data = fetch_data(self)
        
        if len(data) > 0:
            conn = pymysql.connect(
                host="43.156.247.50",  # 替换为实际的主机名
                user="root",  # 替换为实际的用户名
                password="520777",  # 替换为实际的密码
                database="house"  # 替换为实际的数据库名
            )
            
            for house in data:
                owners = owner.getOwner(house['house_id'])
                if len(owners) > 0:
                    cursor = conn.cursor()
                    
                    for owner in owners:
                        # 构建插入数据的 SQL 查询
                        sql = "INSERT INTO house.house_follow(follow_id, house_id, log_type, stars, user_name, content, create_time, create_user_id) VALUES(%s, %s, %s, %s, %s, %s, %s, %s);"
                        values = (
                            flower['id'],
                            house_id,
                            flower['logType'],
                            flower['stars'],
                            flower['userName'],
                            escaped_str,
                            flower['createTimeString'],
                            flower['createUserId']
                        )

                        # 执行 SQL 查询
                        cursor.execute(sql, values)
                        conn.commit()
                    
                    cursor.close()
                    conn.close()
                    
                    pass
